#!/usr/bin/php -q
<?php
/**
 * 利用反向工程从数据库information_schema中生成所有表的创建脚本
 */
$filename = "databases";
$handle = fopen($filename, "r");
$contents = fread($handle, filesize($filename));
fclose($handle);
$dbs = json_decode($contents);
foreach ($dbs as $db) {
	echo 'Server: ', $db->host, PHP_EOL, 'Port:', $db->port, PHP_EOL, 'Database:', $db->name, PHP_EOL;
	if (!($db === end($dbs))) {
		echo '----------------------------------------', PHP_EOL;
	}
	chdir($db->path);
	writefile($db->host, $db->port, $db->name);
}

/**
 * TODO: Add parameters for host, user, port and password
 */
function writefile($host, $port, $db)
{
	$connection = new PDO("mysql:host={$host};dbname=information_schema;port={$port}", 'root', 'changeme');
	$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$connection->exec('set names utf8');
	
	$sql_tables = "SELECT TABLE_NAME,TABLE_COMMENT,TABLE_COLLATION,ENGINE FROM `TABLES` WHERE `TABLE_SCHEMA`=:schema AND TABLE_TYPE='BASE TABLE' AND TABLE_NAME<>'2016/09/20';";
	$stmt_tables = $connection->prepare($sql_tables);
	$stmt_tables->execute([':schema'=>$db]);
	
	$tables = $stmt_tables->fetchAll(PDO::FETCH_ASSOC);
	
	/**
	 * Regular Expression to Replace INT(#) TO INT
	 */
	$pattern = '/((?:INT|BIGINT|TINYINT|SMALLINT|MEDIUMINT|TIMESTAMP))\s*\(\d+\)/';

	$sql_indexes = "SELECT table_name,index_name,GROUP_CONCAT(CONCAT(' `',column_name,'`') ORDER BY seq_in_index) `columns`,index_comment,non_unique FROM information_schema.statistics WHERE table_schema=:schema GROUP BY 1,2;";
	$stmt_indexes = $connection->prepare($sql_indexes);
	$stmt_indexes->execute([':schema'=>$db]);
	$indexes = $stmt_indexes->fetchAll(PDO::FETCH_ASSOC);
	
	$search = ["\r\n", "\n", "\r"];
	$replacement = '';
	foreach ($tables as $table) {
		$table_name = $table['TABLE_NAME'];
		$table_comment = $table['TABLE_COMMENT'];
		$table_comment = ltrim(rtrim(str_replace($search, $replacement, $table_comment)));
		$table_collation = $table['TABLE_COLLATION'];
		$table_engine = $table['ENGINE'];
		$table_charset = explode('_', $table_collation)[0];
		
		$pk = array_filter($indexes, function($value, $key) use ($table_name) {
			return $value['table_name'] == $table_name && $value['index_name'] == 'PRIMARY';
		}, ARRAY_FILTER_USE_BOTH);
	
		if ($pk) {
			$pk = reset($pk);
		}
	
		$table_indexes = array_filter($indexes, function($value, $key) use ($table_name) {
			return $value['table_name'] == $table_name && $value['index_name'] <> 'PRIMARY' && $value['non_unique'] == 1;
		}, ARRAY_FILTER_USE_BOTH);
	
		$table_uniques = array_filter($indexes, function($value, $key) use ($table_name) {
			return $value['table_name'] == $table_name && $value['index_name'] <> 'PRIMARY' && $value['non_unique'] == 0;
		}, ARRAY_FILTER_USE_BOTH);
	
		$sql_columns = "SELECT COLUMN_NAME,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_KEY,COLUMN_TYPE,COLUMN_COMMENT,EXTRA FROM COLUMNS WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table ORDER BY ORDINAL_POSITION;";
		$stmt_columns = $connection->prepare($sql_columns);
		$stmt_columns->execute([':schema'=>$db, ':table' => $table_name]);
		$columns = $stmt_columns->fetchAll(PDO::FETCH_ASSOC);
		$column_names = array_column($columns, 'COLUMN_NAME');
		$lengths = array_map("strlen", $column_names);
		$length = max($lengths);
	
		$handle = fopen("{$table_name}.sql", "w+");
		if(!$handle) die("Can't open file {$table_name}.sql for write.\n");
		fwrite($handle, "USE `{$db}`;\n\n");
		fwrite($handle, "CREATE TABLE IF NOT EXISTS `{$table_name}`\n");
		fwrite($handle, "(\n");
		foreach ($columns as $column) {
			$column_name     = $column['COLUMN_NAME'];
			$column_nullable = $column['IS_NULLABLE'];
			$column_key      = $column['COLUMN_KEY'] == 'PRI' ? '主键' : '';
			$column_comment  = $column['COLUMN_COMMENT'];
			$column_comment  = ltrim(rtrim(str_replace($search, $replacement, $column_comment)));
			$column_type     = strtoupper($column['COLUMN_TYPE']);
			$column_type     = preg_replace($pattern, '${1}', $column_type);
			$column_default  = $column['COLUMN_DEFAULT'];
			$column_extra    = $column['EXTRA'];
			fwrite($handle, "   `{$column_name}`" . str_repeat(" ", $length - strlen($column_name)) . " {$column_type}");
			if ($column_nullable == "NO" || isset($column_default)) {
				fwrite($handle, " NOT NULL");
			}
			if (isset($column_default)) {
				if (preg_match('/^(?:CHAR|VARCHAR|TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT)/', $column_type)) {
					fwrite($handle, "\n   " . str_repeat(" ", $length + 2) . " DEFAULT '" . $column_default . "'");
				}
				else {
					fwrite($handle, "\n   " . str_repeat(" ", $length + 2) . " DEFAULT " . $column_default . "");
				}
			}
			if ($column_extra) {
				fwrite($handle, "\n   " . str_repeat(" ", $length + 2) . " " . strtoupper($column_extra));
			}
			if ($column_comment) {
				fwrite($handle, "\n   " . str_repeat(" ", $length + 2) . " COMMENT '" . $column_comment . "'");
			}
			else {
				fwrite($handle, "\n   " . str_repeat(" ", $length + 2) . " COMMENT ''");
			}
			if ($column === end($columns)) {
				if ($pk) {
					fwrite($handle, ",\n   PRIMARY KEY (" . ltrim($pk['columns']) . ")\n");
				}
				else {
					fwrite($handle, "\n");
				}
			}
			else {
				fwrite($handle, ",\n");
			}
		}
		
		fwrite($handle, ")\n");
		fwrite($handle, "ENGINE = InnoDB\n");
		fwrite($handle, "COMMENT = '{$table_comment}'\n");
		fwrite($handle, "CHARACTER SET = 'utf8mb4'\n");
		fwrite($handle, ";\n");
		fwrite($handle, "\n");
		
		$sequence = 1;
		if ($table_indexes) {
			foreach ($table_indexes as $index) {
				$index_comment = ltrim(rtrim($index['index_comment']));
				fwrite($handle, "CREATE INDEX `index_{$sequence}` ON `{$table_name}` (" . ltrim($index['columns']) . ") COMMENT '{$index_comment}';\n");
				$sequence++;
			}
			fwrite($handle, "\n");
		}
	
		$sequence = 1;
		if ($table_uniques) {
			foreach ($table_uniques as $unique) {
				$index_comment = ltrim(rtrim($unique['index_comment']));
				fwrite($handle, "CREATE UNIQUE INDEX `unique_{$sequence}` ON `{$table_name}` (" . ltrim($unique['columns']) . ") COMMENT '{$index_comment}';\n");
				$sequence++;
			}
			fwrite($handle, "\n");
		}
		fclose($handle);
	}
}
